/**
 * 项目名：化工企业安全生产信息化管理平台
 * 日期：    2020-03-17 14:00:30
 * Copyright (c) 2015- -版权所有
 */

package com.joysuch.wwyt.edu.repository;

import com.joysuch.wwyt.edu.entity.EduCertificate;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;

import java.util.Date;
import java.util.List;

/**
 * EduCertificateDao
 */
public interface EduCertificateDao
        extends JpaRepository<EduCertificate, Long>, QuerydslPredicateExecutor<EduCertificate> {
    public Page<EduCertificate> findAll(Specification<EduCertificate> spec, Pageable pageable);

    public EduCertificate save(EduCertificate bean);

    public void delete(EduCertificate bean);

    @Query("select count(m) from EduCertificate m where code = ?1")
    long countOfCode(String code);

    @Query("select count(m) from EduCertificate m where code = ?1 and id != ?2")
    long countOfCodeExcludeId(String code, Long id);

    @Query("select count(m) from EduCertificate m where name = ?1")
    long countOfName(String name);

    @Query("select count(m) from EduCertificate m where name = ?1 and id != ?2")
    long countOfNameExcludeId(String name, Long id);

    @Query("select name from EduCertificate where id = ?1")
    public String getNameById(Long id);

    @Query(value = "select ec from EduCertificate ec left join EduCertificateUser ecu on ec.id=ecu.certId where ecu.userId=:userId" +
            " and ec.deleteFlag='0' and ecu.deleteFlag='0' and ecu.userContractorFlag = :userContractorFlag and " +
            "(ecu.outOfDateTime is null or ecu.outOfDateTime >= :nowDate)")
    List<EduCertificate> getCertByUserId(Long userId, Integer userContractorFlag, Date nowDate);

    @Query("select code from EduCertificate where id = ?1")
    String getCodeById(Long codeCertId);

    @Query(value = "select c from EduCertificate c  where   c.orgCode like ?1% and c.tenentId= ?2 " +
            " and c.deleteFlag='0' ")
    List<EduCertificate> findByCompany(String companyCode, Long tenentId);

    @Query(value = "SELECT * FROM wwyt_edu_certificate WHERE DELETE_FLAG ='0' ORDER BY NAME ASC,ID DESC", nativeQuery = true)
    List<EduCertificate> getSimpleList();

    @Query(value = "SELECT * FROM wwyt_edu_certificate WHERE USE_STATE = ?1 AND DELETE_FLAG ='0' ORDER BY NAME ASC,ID DESC", nativeQuery = true)
    List<EduCertificate> getSimpleListByUseState(Integer useState);

    @Query("select m.id from EduCertificate m where m.name = ?1")
    Long findByName(String name);
}
